"""
导出zabbix中资源使用第三个版本。
在第四个版本中，对存储卷监控获取历史范围内的数据波动。
"""
import abc
import time
from mytool.zabbix.data_export import database
from mytool.system.date import MyDate
from mytool.office.excel import SingleExcel

db = database.DB(host='172.23.8.160', user='root', password='Mysql@241', db='zabbix')
table_mapping = {
    0: 'trends',        # float
    3: 'trends_uint'    # int
}

# 这两个参数用于指定历史数据的时间范围，start_time的时间等于end_time - start_time.
start_time = 60 * 60 * 24
end_time = None


class Host:
    def __init__(self):
        self.host_info = """select h.name, h.host, i.ip from hosts h
                        join interface i on h.hostid = i.hostid
                        where h.hostid = {}
                        """
        self.single_host = """select h.name, h.hostid, h.status, i.ip from hosts h
                            join interface i on h.hostid = i.hostid
                            where h.hostid = '{}'"""
        self.all_hosts = """select hg.hostid, g.name as groupname, g.groupid from hosts_groups hg
                        join groups g on hg.groupid = g.groupid
                        where g.name regexp '[0-9]_'"""
                        # where g.name regexp '[0-9]_'"""

    def get_all_hosts(self, sql=None):
        if sql:
            return db.query(sql)
        else:
            return db.query(self.all_hosts)

    def get_single_host(self, hostid):
        return db.query(self.single_host.format(hostid))

    def get_host_info(self, hostid):
        _host_info = db.query(self.host_info.format(hostid))
        _host_info = _host_info[0]
        if _host_info:
            return [_host_info['name'], _host_info['ip']]
        else:
            assert ValueError


class History(metaclass=abc.ABCMeta):
    """
    历史数据需要提供一个取样范围，这个时间取样仅仅是通过limit来获取范围内的值，而不是通过时间范围来取值。
    """
    def __init__(self):
        """
        region是一个取样范围。
        """
        self.sqls = []
        self.trends = """select itemid, clock, value_min, value_max from {}
                        where itemid = {} and clock > {} and clock < {}"""
        self.table_name = None

    def get_history(self, hostid):
        all_item = []
        history_data = []

        for sql in self.sqls:
            item = db.query(sql.format(hostid))
            if not item:
                continue
            for each in item:
                all_item.append(each)
                self.table_name = table_mapping[each['value_type']]
                query_data = db.query(self.trends.format(self.table_name, each['itemid'], start_time, end_time))
                if not query_data:
                    continue
                history_data.append(query_data)
            # self.table_name = table_mapping[item[0]['value_type']]
            # query_data = db.query(self.trends.format(self.table_name, item[0]['itemid'], start_time, end_time))
            # print(history_data)
            # if query_data:
            #     history_data.append(query_data)
        return self._data_handle(all_item, history_data)

    @abc.abstractmethod
    def _data_handle(self, item_variety, _history):
        pass

    def _round(self, _history):
        """
        else中进行单位换算。
        """
        if isinstance(_history, list):
            rounded_history = []
            for per in _history:
                rounded_history.append(round(per, 2))
            return rounded_history
        else:
            unit = {
                0: '',
                1: 'KB',
                2: 'MB',
                3: 'GB',
                4: 'TB'
            }
            for i in range(4):
                if _history > 1024:
                    _history = _history / 1024
                else:
                    break
            return str(round(_history, 2)) + unit[i]


class MemHistory(History):
    """
    内存历史数据已实现取样范围。
    """
    def __init__(self):
        super().__init__()
        self.used_mem_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vm.memory.size.*(pav|pused)' and hostid = {}"""
        self.sqls = [self.used_mem_itemid]

    def _data_handle(self, itemid, history_data):
        """
        内存取样数据全部按照已使用来进行输出。
        """
        # print('mem', history_data)
        values = []

        if not history_data:
            return [0, 0, 0]

        key = itemid[0]['key_']
        for data in history_data:
            min_data = []
            max_data = []
            for every_data in data:
                if 'pavailable' in key:
                    min_data.append(100 - every_data['value_min'])
                    max_data.append(100 - every_data['value_max'])
                elif 'pused' in key:
                    min_data.append(every_data['value_min'])
                    max_data.append(every_data['value_max'])
            r_min = round(min(min_data), 2)
            r_max = round(max(max_data), 2)
            values.append(r_min)
            values.append(r_max)
            values.append(abs(r_max - r_min))

        return values


class DiskHistory(History):
    def __init__(self):
        super().__init__()
        self.used_disk_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vfs.fs.size.*,used' and hostid = {}"""
        self.total_disk_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vfs.fs.size.*,total' and hostid = {}"""
        self.sqls = [self.used_disk_itemid, self.total_disk_itemid]
        # self.trends = """select clock, value_max from {} where itemid = {} order by clock desc limit 1"""

    def _data_handle(self, itemid, _history):
        total = []
        for _item_variety in itemid:
            _sum = 0
            for _item in _item_variety:
                key_ = _item['key_']
                if len(key_.split('/')) > 2:    # 盘符超过2个以上的/就不是我们需要的监控项，直接跳过。
                    continue
                disk = db.query(self.trends.format(self.table_name, _item['itemid']))
                if not disk:
                    continue
                disk = disk[0] if disk else disk
                if disk['clock'] > end_time:
                    _sum += int(disk['value_max'])
            _sum = round(_sum / 1024 / 1024 / 1024, 2)
            total.append(_sum)
        return total


class CPUHistory(History):
    def __init__(self):
        super().__init__()
        self.win_cpu = """select itemid, key_, value_type from items
                        where key_ = 'system.cpu.load[all,avg1]' and hostid = {}"""
        self.linux_cpu = """select itemid, key_, value_type from items
                        where key_ like 'perf_counter%Processor%' and hostid = {}"""
        self.sqls = [self.win_cpu, self.linux_cpu]

    def _data_handle(self, itemid, history_data):
        values = []
        # print('cpu', history_data)
        if not history_data:
            return [0, 0, 0]

        for data in history_data:
            min_data = []
            max_data = []
            for every in data:
                min_data.append(every['value_min'])
                max_data.append(every['value_max'])
            r_min = round(min(min_data), 2)
            r_max = round(max(max_data), 2)
            values.append(r_min)
            values.append(r_max)
            values.append(r_max - r_min)

        return values


class IntHistory(History):
    def __init__(self):
        super().__init__()
        self.win_in_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ like 'perf_counter%Network%Received%'"""
        self.win_out_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ like 'perf_counter%Network%Sent%'"""
        self.linux_in_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ regexp 'net.if.in.(ens.*|eth.*|eno.*)' and flags = 4"""
        self.linux_out_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ regexp 'net.if.out.(ens.*|eth.*|eno.*)' and flags = 4"""
        self.if_total_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ regexp 'net.if.total.(ens.*|eth.*|eno.*)' and flags = 4"""
        self.sqls = [self.win_in_itemid, self.win_out_itemid, self.linux_in_itemid, self.linux_out_itemid]

    def _data_handle(self, itemid, history_data):
        # print('int', history_data)
        values = []
        if not history_data:
            return [0, 0, 0, 0, 0, 0]

        for data in history_data:
            min_data = []
            max_data = []
            for every in data:
                min_data.append(every['value_min'])
                max_data.append(every['value_max'])
            r_min = round(min(min_data), 2)
            r_max = round(max(max_data), 2)
            values.append(r_min)
            values.append(r_max)
            values.append(r_max - r_min)
        return values


class StoageHistory(History):
    """
    用于160监控存储。
    """
    def __init__(self):
        super().__init__()
        self.stoage = """select itemid, key_, value_type from items
        where hostid = {} and key_ like 'storage%' and value_type = 0;"""
        self.sqls = [self.stoage]

    def _data_handle(self, items, history):
        all_value = []
        # print(items)
        # print('* ' * 50)
        # print(history)
        # print('* ' * 50)
        for item in items:
            value_max = []
            value_min = []
            for _history in history:
                if item['itemid'] == _history[0]['itemid']:
                    for hi in _history:
                        value_max.append(hi['value_max'])
                        value_min.append(hi['value_min'])
                    all_value.append([item['key_'], min(value_min), max(value_max), max(value_max) - min(value_min)])
                    break
        return all_value
            # max_value = []
            # min_value = []
            # for hi in history[n]:
            #     max_value.append(hi['value_max'])
            #     min_value.append(hi['value_min'])
            # all_value.append([item['key_'], min(min_value), max(max_value)])


def main(excel, vms):
    cpu_hist = CPUHistory()
    mem_hist = MemHistory()
    int_hist = IntHistory()
    hosts = Host()
    data = []
    no_data = []

    # cpu_data.append(['虚拟机名', 'IP地址', 'CPU最小值', 'CPU最大值', 'CPU波动差', '内存最小值', '内存最大值', '内存波动差',
    #                  '网络接收最小值', '网络接收最大值', '网络接收波动差', '网络发送最小值', '网络发送最大值', '网络发送波动值'])
    for n, vm_name in enumerate(vms):
        every_data = []
        _cpu_statistic = []
        # print(vm_name)
        _hostid = hosts.get_single_host(vm_name['hostid'])
        # print(_hostid)
        # 放弃获取禁用的虚拟机。
        if _hostid[0]['status'] != 0:
            continue
        if _hostid:
            _hostid = _hostid[0]['hostid']
        else:
            no_data.append([vm_name])
            continue

        # for info in hosts.get_host_info(_hostid):
        #     every_data.append(info)
        #     _cpu_statistic.append(info)
        data = StoageHistory().get_history(_hostid)
        # every_data += cpu_hist.get_history(_hostid)
        # every_data += mem_hist.get_history(_hostid)
        # every_data += int_hist.get_history(_hostid)
    print(data)
    excel.write_all_sheet(data, 'CPU内存网络历史数据')
    excel.write_all_sheet(no_data, 'no_history')


def from_excel():
    """
    从excel表格中读取虚拟机清单。
    """
    # vm_data = []
    # se = SingleExcel(read_path='g:\\虚拟机清单.xlsx', read_column='f', set_read_sheet='内存分配超过32G虚拟机清单', start=2)
    # for b in se.data:
    #     vm_data.append(b[0])
    vm_data = [{'hostid': 13974}]
    return vm_data


def from_database():
    """
    从数据库中获取虚拟机清单。
    """
    host = Host()
    return host.get_all_hosts()


def main_stoage():
    host = Host()
    vm_data = host.get_all_hosts('select hostid from hosts where name = "zabbix_server"')
    # print(vm_data)
    mydate = MyDate()
    file_date = str(mydate.month()) + '-' + str(mydate.day())
    file_name = 'export_zabbix_{}.xlsx'.format(file_date)
    path = 'g:\\'
    excel = SingleExcel(write_path=path + file_name)
    excel.add_sheet('CPU内存网络历史数据')
    excel.add_sheet('no_history')
    main(excel, vm_data)


if __name__ == '__main__':
    end_time = int(time.time())
    start_time = end_time - start_time
    main_stoage()
    # vm_data = from_database()
    # print(vm_data)
    # mydate = MyDate()
    # file_date = str(mydate.month()) + '-' + str(mydate.day())
    # file_name = 'export_zabbix_{}.xlsx'.format(file_date)
    # path = 'g:\\'
    # excel = SingleExcel(write_path=path + file_name)
    # excel.add_sheet('CPU内存网络历史数据')
    # excel.add_sheet('no_history')
    # end_time = mydate - '4h'
    # main(excel, vm_data)
